﻿CREATE SCHEMA pca_tareas;

CREATE TABLE pca_tareas.desencadenadores
(
	desencadenador_id integer NOT NULL,
	nombre varchar(50) NOT NULL,
	CONSTRAINT desencadenador_pk PRIMARY KEY(desencadenador_id)
);

--Lunes Martes Miercoles ...
CREATE TABLE pca_tareas.dias_semana
(
	dia_semana_id integer NOT NULL,
	nombre varchar(50) NOT NULL,
	CONSTRAINT dias_semana_pk PRIMARY KEY(dia_semana_id)
);

--1 2 3 ...
CREATE TABLE pca_tareas.dias_mes
(
	dia_mes_id integer NOT NULL,
	nombre varchar(50) NOT NULL,
	CONSTRAINT dias_mes_pk PRIMARY KEY(dia_mes_id)
);

--Enero Febrero Marzo ...
CREATE TABLE pca_tareas.meses
(
	mes_id integer NOT NULL,
	nombre varchar(50) NOT NULL,
	CONSTRAINT meses_pk PRIMARY KEY(mes_id)
);

CREATE SEQUENCE pca_tareas.seq_alerta_id;
CREATE TABLE pca_tareas.alertas
(
	empresa_id varchar(2) NOT NULL,
	oficina_id varchar(2) NOT NULL,
	alerta_id bigint NOT NULL DEFAULT pca_global.fn_pseudo_encrypt_bigint(nextval('pca_tareas.seq_alerta_id')::integer),
	sesion_id bigint NOT NULL,
	usuario_id character(36) NOT NULL,--El usuario del registro la alerta
	fecha_hora_sistema timestamp with time zone NOT NULL DEFAULT now(),
	fecha_hora_actualizacion timestamp with time zone NOT NULL DEFAULT now(),
	mensaje varchar(500) NOT NULL,--El mensaje a mostrar en pantalla
	desencadenador_id integer NOT NULL,
	meses integer[] NOT NULL,
	dias integer[] NOT NULL,
	activo boolean NOT NULL DEFAULT true,
	CONSTRAINT alarmas_pk PRIMARY KEY(empresa_id,oficina_id,alerta_id),
	CONSTRAINT oficinas_fkey FOREIGN KEY(empresa_id,oficina_id)
		REFERENCES pca.oficinas(empresa_id,oficina_id) MATCH FULL
		ON UPDATE CASCADE ON DELETE CASCADE,
	CONSTRAINT sesiones_fkey FOREIGN KEY (empresa_id,oficina_id,sesion_id)
		REFERENCES pca_log.sesiones (empresa_id,oficina_id,sesion_id) MATCH FULL
		ON UPDATE CASCADE ON DELETE CASCADE,
	CONSTRAINT usuarios_fkey FOREIGN KEY(usuario_id)
		REFERENCES pca_membership."Users"("pId") MATCH SIMPLE
		ON UPDATE CASCADE ON DELETE CASCADE,
	CONSTRAINT desencadenadores_fkey FOREIGN KEY(desencadenador_id)
		REFERENCES pca_tareas.desencadenadores(desencadenador_id) MATCH SIMPLE
		ON UPDATE RESTRICT ON DELETE RESTRICT
);

CREATE OR REPLACE FUNCTION pca_caja.fn_update_fecha_hora_actualizacion_alerta() RETURNS trigger AS
$$
BEGIN
	NEW.fecha_hora_actualizacion := now();
	RETURN NEW;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER tri_update_fecha_hora_actualizacion_alerta
	BEFORE UPDATE 
	ON pca_tareas.alertas
	FOR EACH ROW
	EXECUTE PROCEDURE pca_caja.fn_update_fecha_hora_actualizacion_alerta();

-------------------------------------------------------------------------------------------------------------
------------------------------------------------Vistas-------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
CREATE OR REPLACE VIEW pca_tareas.vw_alertas AS
SELECT 
	a.empresa_id,
	a.oficina_id,
	a.alerta_id,
	a.sesion_id,
	a.usuario_id,
	(SELECT "UsersProfileData"."Nick" FROM pca_membership."UsersProfileData" WHERE "UsersProfileData"."pId" = a.usuario_id) AS usuario,
	a.fecha_hora_sistema,
	a.fecha_hora_actualizacion,
	a.mensaje,
	a.desencadenador_id,
	(SELECT nombre FROM pca_tareas.desencadenadores WHERE desencadenador_id = a.desencadenador_id)AS desencadenador,
	a.meses,
	a.dias,
	a.activo
FROM
	pca_tareas.alertas a;

CREATE OR REPLACE VIEW pca_tareas.vw_alertas_dia AS
SELECT
	*
FROM
	pca_tareas.vw_alertas
WHERE
	desencadenador_id=1
UNION
SELECT
	*
FROM
	pca_tareas.vw_alertas
WHERE
	desencadenador_id = 2 AND
	EXTRACT(MONTH FROM current_timestamp) = ANY(meses) AND
	EXTRACT(DOW FROM current_timestamp) = ANY(dias)
UNION
SELECT
	*
FROM
	pca_tareas.vw_alertas
WHERE
	desencadenador_id = 3 AND
	EXTRACT(MONTH FROM current_timestamp) = ANY(meses) AND
	EXTRACT(DAY FROM current_timestamp) = ANY(dias);